package com.css.fxfzysx.modules.cztDataMange.repository.impl;

import com.css.fxfzysx.constants.RelationConstants;
import com.css.fxfzysx.modules.cztDataMange.entity.CbvInfoEntity;
import com.css.fxfzysx.modules.cztDataMange.repository.BasicLibraryRepository;
import com.css.fxfzysx.modules.cztDataMange.vo.VulnerabilityDataVo;
import com.css.fxfzysx.modules.vabStructure.param.CustomVulnerabilityParam;
import com.css.fxfzysx.modules.vabStructure.param.PageParam;
import com.css.fxfzysx.util.CreateGson;
import com.css.fxfzysx.util.PlatformObjectUtils;
import com.css.fxfzysx.zcpt.sys.entity.SUser;
import com.css.fxfzysx.zcpt.sys.service.SUserService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Repository
public class BasicLibraryRepositoryImpl implements BasicLibraryRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SUserService sUserService;

    @Override
    public HashMap<String, Object> queryBasicLibrary(CustomVulnerabilityParam param, int page, int size,int role,String province) {
        String relation = RelationConstants.getConstant(param.getProvince());
        if(role == 1){
            relation = RelationConstants.getConstant(province);
        }
        StringBuilder sb = new StringBuilder("select *");
        StringBuilder count = new StringBuilder("select count(pid) ");
        StringBuilder sql = new StringBuilder(" from( SELECT NUMBER , cpvi_id AS pid, province, city, county, structure_type0 AS structure_type, structure_type1, years, fortification, purpose0 AS use0, purpose1 AS use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_probability_vulnerability_info UNION ALL SELECT NUMBER, chbpi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_high_building_param_info UNION ALL SELECT NUMBER, cal_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user_id as create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_crs_calentirety_result UNION ALL SELECT NUMBER, cbcbi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user, create_time, floor as cs, basic_library, del_flag, data_type FROM czt_brick_concrete_building_info UNION ALL SELECT NUMBER , matrix_id AS pid, province, city, county, structure_type, structure_type1, years, fortification, use0, use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM czt_building_matrix UNION ALL SELECT NUMBER , cbvi_id AS pid, province, city, county, structure_type, structure_type1, years, fortification, use0, use1, create_user, create_time, cs, basic_library, del_flag, data_type FROM "+relation+") result_Data WHERE del_flag = '0' AND basic_library = '1'");
        if (!PlatformObjectUtils.isEmpty(param.getProvince())) {
            sql.append(" and province ='" + param.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCity())) {
            sql.append(" and city ='" + param.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCounty())) {
            sql.append(" and county ='" + param.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            sql.append(" and years ='" + param.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCreateStartTime())) {
            sql.append(" and create_time >= '" + param.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCreateEndTime())) {
            String endTime = param.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStructureType())) {
            sql.append(" and structure_type ='" + param.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStructureType1())) {
            sql.append(" and structure_type1 ='" + param.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getFortification())) {
            sql.append(" and fortification ='" + param.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getUse0())) {
            sql.append(" and use0 ='" + param.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getUse1())) {
            sql.append(" and use1 ='" + param.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCs())) {
            sql.append(" and cs ='" + param.getCs()+"'");
        }
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.queryForObject(count.append(sql).toString(),Integer.class);
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sb.append(sql).append(orderSql).append(limitSql);
        List<CbvInfoEntity> data = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(CbvInfoEntity.class));
        List<CbvInfoEntity> list = new ArrayList<>();
        for (CbvInfoEntity datum : data) {
            if (PlatformObjectUtils.isNotEmpty(datum.getCreateUser())) {
                String user = sUserService.getSUser(datum.getCreateUser());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                datum.setUserName(sUser.getUserName());
                if(StringUtils.isNotBlank(param.getCreateUser())){
                    if(!sUser.getUserName().equals(param.getCreateUser())){
                        continue;
                    }
                }
            }

            if (PlatformObjectUtils.isNotEmpty(datum.getProvince()) && PlatformObjectUtils.isNotEmpty(datum.getCity()) && PlatformObjectUtils.isNotEmpty(datum.getCounty())) {
                StringBuilder region = new StringBuilder(datum.getProvince()).append("-").append(datum.getCity()).append("-").append(datum.getCounty());
                datum.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())) {
                StringBuilder st = new StringBuilder(datum.getStructureType());
                if(PlatformObjectUtils.isNotEmpty(datum.getStructureType1())){
                    st.append("-"+datum.getStructureType1());
                }
                datum.setSt(st.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getUse0()) && PlatformObjectUtils.isNotEmpty(datum.getUse1())) {
                StringBuilder use = new StringBuilder(datum.getUse0()).append("-").append(datum.getUse1());
                datum.setUse(use.toString());
            }
            list.add(datum);
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", list.size());
        resultMap.put("rows", list);
        return resultMap;
    }

    @Override
    public void moveBvById(String id,String province) {
        String sql = "update "+RelationConstants.getConstant(province)+" set basic_library= '0' where cbvi_id = ?";
        jdbcTemplate.update(sql,id);
    }

    @Override
    public void movePvById(String id) {
        String sql = "update czt_probability_vulnerability_info set basic_library= '0' where cpvi_id = ?";
        jdbcTemplate.update(sql,id);
    }

    @Override
    public HashMap<String, Object> page(PageParam param) {
        StringBuilder sb = new StringBuilder("select *");
        StringBuilder count = new StringBuilder("select count(pid) ");
        StringBuilder sql = new StringBuilder(" from( SELECT NUMBER , cpvi_id AS pid, province, city, county, structure_type0 AS structure_type, structure_type1, years, fortification, purpose0 AS use0, purpose1 AS use1, create_user, create_time, cs as number_of_floors, basic_library, del_flag, data_type FROM czt_probability_vulnerability_info UNION ALL SELECT NUMBER, chbpi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user, create_time, cs as number_of_floors, basic_library, del_flag, data_type FROM czt_high_building_param_info UNION ALL SELECT NUMBER, cal_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user_id as create_user, create_time, cs as number_of_floors, basic_library, del_flag, data_type FROM czt_crs_calentirety_result UNION ALL SELECT NUMBER, cbcbi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, years, fortification, purpose AS use0, purpose1 AS use1, create_user, create_time, floor as number_of_floors, basic_library, del_flag, data_type FROM czt_brick_concrete_building_info UNION ALL SELECT NUMBER , matrix_id AS pid, province, city, county, structure_type, structure_type1, years, fortification, use0, use1, create_user, create_time, cs as number_of_floors, basic_library, del_flag, data_type FROM czt_building_matrix UNION ALL SELECT NUMBER , cbvi_id AS pid, province, city, county, structure_type, structure_type1, years, fortification, use0, use1, create_user, create_time, cs as number_of_floors, basic_library, del_flag, data_type FROM "+RelationConstants.getConstant(param.getProvince())+") result_Data WHERE del_flag = '0' AND basic_library = '1'");
        if (!PlatformObjectUtils.isEmpty(param.getProvince())) {
            sql.append(" and province ='" + param.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCity())) {
            sql.append(" and city ='" + param.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getCounty())) {
            sql.append(" and county ='" + param.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            sql.append(" and years ='" + param.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStartTime())) {
            sql.append(" and create_time >= '" + param.getStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getEndTime())) {
            String endTime = param.getEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStructureType())) {
            sql.append(" and structure_type ='" + param.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getStructureType1())) {
            sql.append(" and structure_type1 ='" + param.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getFortification())) {
            sql.append(" and fortification ='" + param.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getUse0())) {
            sql.append(" and use0 ='" + param.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getUse1())) {
            sql.append(" and use1 ='" + param.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(param.getNumber())) {
            sql.append(" and number_of_floors ='" + param.getNumber()+"'");
        }
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.queryForObject(count.append(sql).toString(),Integer.class);
        StringBuilder limitSql = new StringBuilder(" limit " + param.getRows() + " offset " + (param.getPage() - 1) * param.getRows());
        sb.append(sql).append(orderSql).append(limitSql);
        List<VulnerabilityDataVo> data = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(VulnerabilityDataVo.class));
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }
}
